package com.robot.admin.dao.vo;

import com.robot.admin.pojo.Admin;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * 中间dao,用于后台admin代理商提现管理下的crud
 *
 * @author wuyunbin
 */
public interface AgentDao extends JpaRepository<Admin, String>, JpaSpecificationExecutor<Admin> {

    /**
     * 两表查询昨天的订单列表用作定时任务中新增结算总表和结算明细
     *
     * @return
     */
    @Query(value = "select concat(date_add(curdate(), interval -1 day))   as date,\n" +
            "       agent.id                                       as agentId,\n" +
            "       concat(agent.province, agent.city, agent.area) as agentArea,\n" +
            "       agent.real_name                                as agentName,\n" +
            "       count(orders.id)                               as totalOrders,\n" +
            "       sum(orders.price)                              as totalPrice,\n" +
            "       sum(orders.pay_amount)                         as totalPayAmount,\n" +
            "       sum(orders.principal)                          as totalPrincipal,\n" +
            "       sum(orders.agent_revenue)                      as totalAgentRevenue,\n" +
            "       '0'                                            as status\n" +
            "from orders.orders,\n" +
            "     agent.agent\n" +
            "where orders.agent_id = agent.id\n" +
            "  and orders.status = 1\n" +
            "  and datediff(orders.created_at, now()) = -1\n" +
            "group by agent.id;", nativeQuery = true)
    public List<Object[]> findYesterdayCashWithdrawals();

    /**
     * 三表查询每天支付成功的订单数,用于后台admin代理商提现管理模块中按天显示提现列表
     *
     * @return
     */
    @Query(value = "select concat(year(orders.created_at), '-', month(orders.created_at), '-', day(orders.created_at)) as date,\n" +
            "       agent.id                                                                                    as agentId,\n" +
            "       concat(agent.province, agent.city, agent.area)                                              as agentArea,\n" +
            "       agent.real_name                                                                             as agentName,\n" +
            "       count(orders.id)                                                                            as totalOrders,\n" +
            "       sum(orders.price)                                                                           as totalPrice,\n" +
            "       sum(orders.pay_amount)                                                                      as totalPayAmount,\n" +
            "       sum(orders.principal)                                                                       as totalPrincipal,\n" +
            "       sum(orders.agent_revenue)                                                                   as totalAgentRevenue,\n" +
            "       (select status\n" +
            "        from orders.settlement\n" +
            "        where settlement.agent_id = agent.id\n" +
            "          and concat(year(orders.created_at), '-', month(orders.created_at), '-', day(orders.created_at)) =\n" +
            "              settlement.settlement_date)                                                          as status\n" +
            "from orders.orders,\n" +
            "     agent.agent\n" +
            "where orders.agent_id = agent.id\n" +
            "  and orders.status = 1\n" +
            "group by concat(year(orders.created_at), '-', month(orders.created_at), '-', day(orders.created_at)), agent.id, status;", nativeQuery = true)
    public List<Object[]> findCashWithdrawals();
}
